﻿namespace HuaWei.CommClass
{
    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.IO;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;

    public class ReadWriterExcel
    {
        private static string[] botbmnerR(string str)
        {
            Microsoft.Office.Interop.Excel.Application application = new ApplicationClass();
            Workbook workbook = application.Workbooks.Open(str, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
            int count = workbook.Worksheets.Count;
            string[] strArray = new string[count];
            for (int i = 1; i <= count; i++)
            {
                strArray[i - 1] = ((Worksheet) workbook.Worksheets[i]).Name;
            }
            return strArray;
        }

        public static System.Data.DataTable GetData(string strPath)
        {
            System.Data.DataTable dataTable = new System.Data.DataTable();
            try
            {
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
                string str2 = "";
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    str2 = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
                }
                new OleDbDataAdapter("select * from [" + str2 + "]", connectionString).Fill(dataTable);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            return dataTable;
        }

        public static System.Data.DataTable GetData(string strPath, string[] fieldName, int rPos)
        {
            System.Data.DataTable dataTable = new System.Data.DataTable();
            System.Data.DataTable table2 = new System.Data.DataTable();
            try
            {
                System.Data.DataTable oleDbSchemaTable;
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
                string str = "";
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    oleDbSchemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    str = oleDbSchemaTable.Rows[0][2].ToString().Trim();
                    OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + str + "] where 1=2", connectionString);
                    adapter.Fill(table2);
                }
                foreach (DataRow row in oleDbSchemaTable.Rows)
                {
                    dataTable.Clear();
                    str = row[2].ToString().Trim();
                    string selectCommandText = "select * from [" + str + "]";
                    if (!str.Contains("$Print_"))
                    {
                        DataRow row2 = null;
                        DataRow row3 = null;
                        row2 = table2.NewRow();
                        row2[0] = smethod(str);
                        row2[1] = smethod1(str);
                        table2.Rows.Add(row2);
                        new OleDbDataAdapter(selectCommandText, connectionString).Fill(dataTable);
                        for (int i = rPos; i < dataTable.Rows.Count; i++)
                        {
                            row2 = table2.NewRow();
                            row3 = dataTable.Rows[i];
                            string str4 = row3[fieldName[0]].ToString();
                            int result = 0;
                            if (str4.Equals("") || ((!str4.Contains("D") && !str4.Contains("Z")) && !int.TryParse(str4.Substring(0, 3), out result)))
                            {
                                break;
                            }
                            for (int j = 0; j < fieldName.Length; j++)
                            {
                                row2[j] = row3[fieldName[j]];
                            }
                            table2.Rows.Add(row2);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            return table2;
        }

        public static bool GetData2(string filePath)
        {
            try
            {
                OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
                connection.Open();
                string[] strArray = botbmnerR(filePath);
                if (strArray.Length > 0)
                {
                    foreach (string str2 in strArray)
                    {
                        OleDbCommand command = connection.CreateCommand();
                        command.CommandText = string.Format(" select * from [{0}$]", str2);
                        OleDbDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            if (!(reader[0].ToString() == "序号"))
                            {
                            }
                        }
                        reader.Close();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        [DllImport("User32.dll", CharSet=CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr numptr, out int num);
        public static System.Data.DataTable ImportExcel()
        {
            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                try
                {
                    return GetData(dialog.FileName);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            return null;
        }

        public static DataSet ImportExcel(string file)
        {
            DataSet set;
            FileInfo info = new FileInfo(file);
            if (!info.Exists)
            {
                return null;
            }
            OleDbConnection selectConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'");
            DataSet dataSet = new DataSet();
            try
            {
                selectConnection.Open();
                string selectCommandText = "select * from  [Sheet1$]";
                new OleDbDataAdapter(selectCommandText, selectConnection).Fill(dataSet);
                set = dataSet;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return set;
        }

        private void method(Microsoft.Office.Interop.Excel.Application application)
        {
            application.Quit();
            application = null;
            GC.Collect(0);
        }

        private bool method1(Microsoft.Office.Interop.Excel.Application application)
        {
            try
            {
                if (application != null)
                {
                    application.Quit();
                    Marshal.ReleaseComObject(application);
                    foreach (Process process in Process.GetProcessesByName("EXCEL"))
                    {
                        if (!process.CloseMainWindow())
                        {
                            process.Kill();
                        }
                    }
                    application = null;
                    return true;
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        public static void SaveAsExcel(System.Data.DataTable dtExcel)
        {
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.Filter = "导出Excel (*.xls)|*.xls";
            dialog.FilterIndex = 0;
            dialog.RestoreDirectory = true;
            dialog.CreatePrompt = true;
            dialog.Title = "导出文件保存路径";
            dialog.ShowDialog();
            string fileName = dialog.FileName;
            if (fileName.Length != 0)
            {
                Missing template = Missing.Value;
                Microsoft.Office.Interop.Excel.Application o = new ApplicationClass();
                try
                {
                    o.Application.Workbooks.Add(true);
                    o.Visible = false;
                    if (o == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                    }
                    else
                    {
                        int num;
                        Workbooks workbooks = o.Workbooks;
                        Workbook workbook = workbooks.Add(template);
                        Worksheet activeSheet = (Worksheet) workbook.ActiveSheet;
                        activeSheet.Name = "test";
                        for (num = 0; num < dtExcel.Columns.Count; num++)
                        {
                            o.Cells[1, num + 1] = dtExcel.Columns[num].Caption.ToString();
                        }
                        for (num = 0; num < dtExcel.Rows.Count; num++)
                        {
                            for (int i = 0; i < dtExcel.Columns.Count; i++)
                            {
                                if (dtExcel.Rows[num][i].ToString().GetType() == typeof(string))
                                {
                                    o.Cells[num + 2, i + 1] = "'" + dtExcel.Rows[num][i].ToString().Trim();
                                }
                                else
                                {
                                    o.Cells[num + 2, i + 1] = dtExcel.Rows[num][i].ToString().Trim();
                                }
                            }
                        }
                        activeSheet.SaveAs(fileName, template, template, template, template, template, XlSaveAsAccessMode.xlNoChange, template, template, template);
                        workbook.Close(false, template, template);
                        workbooks.Close();
                        o.Quit();
                        Marshal.ReleaseComObject(activeSheet);
                        Marshal.ReleaseComObject(workbook);
                        Marshal.ReleaseComObject(workbooks);
                        Marshal.ReleaseComObject(o);
                        GC.Collect();
                        MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        Process.Start(fileName);
                    }
                }
                catch (Exception exception)
                {
                    MessageBox.Show(exception.Message, "错误提示");
                }
                finally
                {
                    smethod2(o);
                }
            }
        }

        private static string smethod(string str)
        {
            string str2 = "";
            if (str.Contains("100"))
            {
                return "100";
            }
            if (str.Contains("200"))
            {
                return "200";
            }
            if (str.Contains("300"))
            {
                return "300";
            }
            if (str.Contains("400"))
            {
                return "400";
            }
            if (str.Contains("500"))
            {
                return "500";
            }
            if (str.Contains("600"))
            {
                return "600";
            }
            if (str.Contains("700"))
            {
                return "700";
            }
            if (str.Contains("800"))
            {
                return "800";
            }
            if (str.Contains("900"))
            {
                return "900";
            }
            if (str.Contains("计日工"))
            {
                return "D";
            }
            if (str.Contains("暂定金"))
            {
                str2 = "Z";
            }
            return str2;
        }

        private static string smethod1(string str)
        {
            string str2 = "";
            if (str.Contains("100"))
            {
                return "总则";
            }
            if (str.Contains("200"))
            {
                return "路基";
            }
            if (str.Contains("300"))
            {
                return "路面";
            }
            if (str.Contains("400"))
            {
                return "桥梁、涵洞";
            }
            if (str.Contains("500"))
            {
                return "隧道工程";
            }
            if (str.Contains("600"))
            {
                return "600";
            }
            if (str.Contains("700"))
            {
                return "绿化工程";
            }
            if (str.Contains("800"))
            {
                return "800";
            }
            if (str.Contains("900"))
            {
                return "900";
            }
            if (str.Contains("计日工"))
            {
                return "劳 务";
            }
            if (str.Contains("暂定金"))
            {
                str2 = "暂定金";
            }
            return str2;
        }

        private static void smethod2(Microsoft.Office.Interop.Excel.Application application)
        {
            try
            {
                if (application != null)
                {
                    int num;
                    GetWindowThreadProcessId(new IntPtr(application.Hwnd), out num);
                    Process.GetProcessById(num).Kill();
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine("Delete Excel Process Error:" + exception.Message);
            }
        }
    }
}

